#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
数据库初始化工具
负责执行SQL脚本，创建和更新数据库表结构
"""

import os
import sys
import time
from pathlib import Path
from typing import List, Dict, Any, Optional

# 添加项目根目录到Python路径
sys.path.append(str(Path(__file__).parent.parent.parent))

from app.conf.config import config, logger
from app.utils.db_utils import db


class DatabaseInitializer:
    """
    数据库初始化类
    负责执行SQL脚本，创建和更新数据库表结构
    """
    
    def __init__(self):
        """
        初始化数据库初始化工具
        """
        self.db = db
        self.sql_dir = Path(__file__).parent
        self.logger = logger
    
    def execute_sql_file(self, sql_file_path: str) -> Dict[str, Any]:
        """
        执行SQL文件
        
        Args:
            sql_file_path: SQL文件路径
            
        Returns:
            Dict: 执行结果，包含成功/失败标志、影响行数等
        """
        start_time = time.time()
        result = {
            'file': sql_file_path,
            'success': False,
            'execution_time': 0,
            'error': None
        }
        
        try:
            # 确保SQL文件存在
            if not os.path.exists(sql_file_path):
                raise FileNotFoundError(f"SQL文件不存在: {sql_file_path}")
            
            # 读取SQL文件内容
            with open(sql_file_path, 'r', encoding='utf-8') as f:
                sql_content = f.read()
            
            # 分割SQL语句（处理多条语句）
            sql_statements = self._split_sql_statements(sql_content)
            
            # 执行每条SQL语句
            total_affected_rows = 0
            for stmt in sql_statements:
                stmt = stmt.strip()
                if stmt and not stmt.startswith('--'):
                    # 执行SQL语句
                    if self._is_create_or_alter_table(stmt):
                        # 对于表创建/修改，使用execute而非execute_sql以支持多条语句
                        with self.db._get_connection() as conn:
                            with conn.cursor() as cursor:
                                cursor.execute(stmt)
                                affected_rows = cursor.rowcount
                                conn.commit()
                    else:
                        affected_rows = self.db.execute_sql(stmt, params={})
                    
                    total_affected_rows += affected_rows
            
            # 计算执行时间
            execution_time = time.time() - start_time
            
            # 更新结果
            result.update({
                'success': True,
                'execution_time': round(execution_time * 1000, 2),  # 转换为毫秒
                'total_statements': len(sql_statements),
                'total_affected_rows': total_affected_rows
            })
            
            self.logger.info(f"成功执行SQL文件: {sql_file_path}，共执行{len(sql_statements)}条语句，影响{total_affected_rows}行，耗时{round(execution_time * 1000, 2)}ms")
            
        except Exception as e:
            error_msg = f"执行SQL文件失败 {sql_file_path}: {str(e)}"
            self.logger.error(error_msg)
            result.update({
                'error': str(e),
                'execution_time': round((time.time() - start_time) * 1000, 2)
            })
        
        return result
    
    def init_shopify_tables(self) -> Dict[str, Any]:
        """
        初始化Shopify相关的数据表
        
        Returns:
            Dict: 初始化结果
        """
        self.logger.info("开始初始化Shopify相关数据表...")
        start_time = time.time()
        
        # 初始化Shopify表的SQL文件路径
        sql_file = str(self.sql_dir / "init_shopify_tables.sql")
        
        # 执行SQL文件
        result = self.execute_sql_file(sql_file)
        
        # 计算总执行时间
        total_time = round((time.time() - start_time) * 1000, 2)
        
        final_result = {
            'success': result['success'],
            'details': result,
            'total_execution_time': total_time
        }
        
        if result['success']:
            self.logger.info(f"Shopify数据表初始化成功，总耗时: {total_time}ms")
        else:
            self.logger.error(f"Shopify数据表初始化失败")
        
        return final_result
    
    def check_database_health(self) -> Dict[str, Any]:
        """
        检查数据库连接健康状态
        
        Returns:
            Dict: 健康检查结果
        """
        result = {
            'connected': False,
            'error': None,
            'tables': [],
            'version': None
        }
        
        try:
            # 测试连接
            with self.db._get_connection() as conn:
                # 获取数据库版本
                with conn.cursor() as cursor:
                    cursor.execute("SELECT VERSION()")
                    version = cursor.fetchone()[0]
                    result['version'] = version
                
                # 获取Shopify相关表列表
                with conn.cursor() as cursor:
                    cursor.execute("""
                        SHOW TABLES LIKE 'shopify_%'
                    """)
                    tables = [table[0] for table in cursor.fetchall()]
                    result['tables'] = tables
                
                result['connected'] = True
                self.logger.info(f"数据库健康检查通过: {version}，找到{len(tables)}个Shopify相关表")
                
        except Exception as e:
            error_msg = f"数据库健康检查失败: {str(e)}"
            self.logger.error(error_msg)
            result['error'] = str(e)
        
        return result
    
    def _split_sql_statements(self, sql_content: str) -> List[str]:
        """
        将SQL内容分割为单独的SQL语句
        
        Args:
            sql_content: SQL文件内容
            
        Returns:
            List[str]: SQL语句列表
        """
        # 移除注释和空行
        lines = []
        for line in sql_content.splitlines():
            # 处理行内注释
            if '--' in line:
                line = line.split('--', 1)[0]
            line = line.strip()
            if line:
                lines.append(line)
        
        # 将多行合并为一行
        full_sql = ' '.join(lines)
        
        # 分割SQL语句（分号分隔，但需要考虑字符串内的分号）
        statements = []
        current_stmt = ''
        in_string = None
        
        for char in full_sql:
            # 处理字符串引号
            if char in ['"', "'", '`']:
                if in_string == char:
                    # 检查是否是转义引号
                    if current_stmt and current_stmt[-1] == '\\':
                        current_stmt += char
                    else:
                        in_string = None
                        current_stmt += char
                elif in_string is None:
                    in_string = char
                    current_stmt += char
                else:
                    current_stmt += char
            # 处理分号（非字符串内的分号作为语句分隔符）
            elif char == ';' and in_string is None:
                statements.append(current_stmt.strip())
                current_stmt = ''
            else:
                current_stmt += char
        
        # 添加最后一个语句（如果有的话）
        if current_stmt.strip():
            statements.append(current_stmt.strip())
        
        return statements
    
    def _is_create_or_alter_table(self, sql_stmt: str) -> bool:
        """
        判断SQL语句是否为CREATE TABLE或ALTER TABLE语句
        
        Args:
            sql_stmt: SQL语句
            
        Returns:
            bool: 是否为表创建或修改语句
        """
        sql_lower = sql_stmt.lower()
        return sql_lower.startswith('create table') or sql_lower.startswith('alter table')


# 初始化数据库初始化工具实例
db_initializer = DatabaseInitializer()


# 命令行运行时的主函数
if __name__ == "__main__":
    import argparse
    
    parser = argparse.ArgumentParser(description='数据库初始化工具')
    parser.add_argument('--init-shopify', action='store_true', help='初始化Shopify相关数据表')
    parser.add_argument('--check', action='store_true', help='检查数据库连接状态')
    parser.add_argument('--sql-file', type=str, help='指定SQL文件路径')
    
    args = parser.parse_args()
    
    if args.init_shopify:
        # 初始化Shopify表
        result = db_initializer.init_shopify_tables()
        print(f"初始化结果: {'成功' if result['success'] else '失败'}")
        if not result['success']:
            print(f"错误: {result['details'].get('error', '未知错误')}")
            sys.exit(1)
    
    elif args.check:
        # 检查数据库连接
        result = db_initializer.check_database_health()
        print(f"数据库连接状态: {'已连接' if result['connected'] else '未连接'}")
        if result['connected']:
            print(f"数据库版本: {result['version']}")
            print(f"Shopify相关表数量: {len(result['tables'])}")
            if result['tables']:
                print("表列表:")
                for table in result['tables']:
                    print(f"  - {table}")
        else:
            print(f"错误: {result['error']}")
            sys.exit(1)
    
    elif args.sql_file:
        # 执行指定SQL文件
        result = db_initializer.execute_sql_file(args.sql_file)
        print(f"SQL文件执行结果: {'成功' if result['success'] else '失败'}")
        if result['success']:
            print(f"执行时间: {result['execution_time']}ms")
            print(f"执行语句数: {result['total_statements']}")
            print(f"影响行数: {result['total_affected_rows']}")
        else:
            print(f"错误: {result['error']}")
            sys.exit(1)
    
    else:
        # 默认执行检查
        parser.print_help()
        sys.exit(1)